﻿using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.Common;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

namespace Mysoft.QuickCode.DAL
{
    /// <summary>
    /// 作废的类
    /// </summary>
    class __DBPager__
    {
        #region 基于SQL查询的翻页功能

        /// <summary>
        /// 创建select count语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string CreateSelectCount(string sql)
        {
            Regex regexSelectFrom = new Regex("((^select)|(\\s*select)\\s)|((^from)|(\\s*from)\\s)", RegexOptions.IgnoreCase);
            MatchCollection matchSelectFroms = regexSelectFrom.Matches(sql);
            Stack<Match> stackSelectFrom = new Stack<Match>();
            int startIndex = 0, endIndex = 0;

            //通过出入栈匹配主查询
            foreach (Match sf in matchSelectFroms)
            {
                if (stackSelectFrom.Count == 0
                    && sf.Value.ToLower() == "select")
                {
                    startIndex = sf.Index;
                    stackSelectFrom.Push(sf);
                }
                else
                {
                    switch (sf.Value.Trim().ToLower())
                    {
                        case "select":
                            {
                                stackSelectFrom.Push(sf);
                                break;
                            }
                        case "from":
                            {
                                Match match = stackSelectFrom.Pop();

                                if (Regex.IsMatch(match.Value.ToLower(), "select") && stackSelectFrom.Count == 0)
                                {
                                    //如果from匹配的是select，则表示已经匹配到了主查询语句
                                    endIndex = sf.Index + sf.Value.Length;
                                }
                                break;
                            }
                    }
                    //匹配完成，肯定会找到from字符串的位置
                    if (endIndex > 0)
                    {
                        break;
                    }
                }
            }

            return string.Format("{0} {1} {2}",
                sql.Substring(0, startIndex),
                "SELECT COUNT(1) FROM",
                sql.Substring(endIndex));
        }

        /// <summary>
        /// 创建select row_number语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        static string CreateSelectRowNumber(string sql, string orderBy)
        {
            Regex regexSelectFrom = new Regex("((^select)|(\\s*select)\\s)|((^from)|(\\s*from)\\s)", RegexOptions.IgnoreCase);
            MatchCollection matchSelectFroms = regexSelectFrom.Matches(sql);
            Stack<Match> stackSelectFrom = new Stack<Match>();
            int endIndex = 0;

            //通过出入栈匹配主查询
            foreach (Match sf in matchSelectFroms)
            {
                if (stackSelectFrom.Count == 0
                    && sf.Value.ToLower() == "select")
                {
                    stackSelectFrom.Push(sf);
                }
                else
                {
                    switch (sf.Value.Trim().ToLower())
                    {
                        case "select":
                            {
                                stackSelectFrom.Push(sf);
                                break;
                            }
                        case "from":
                            {
                                Match match = stackSelectFrom.Pop();
                                if (Regex.IsMatch(match.Value.ToLower(), "select") && stackSelectFrom.Count == 0)
                                {
                                    //如果from匹配的是select，则表示已经匹配到了主查询语句
                                    endIndex = sf.Index;
                                }
                                break;
                            }
                    }
                    //匹配完成，肯定会找到from字符串的位置
                    if (endIndex > 0 && stackSelectFrom.Count == 0)
                    {
                        break;
                    }
                }
            }

            return string.Format("{0} {1} {2}",
                sql.Substring(0, endIndex),
                string.Format(", ROW_NUMBER() OVER(ORDER BY {0}) AS _RowNum_", orderBy),
                sql.Substring(endIndex));
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static Pager<T> GetPager<T>(Pager pager, string sqlText, object sqlParams)
            where T : class,new()
        {
            //查询和计算总数SQL
            string sqlCount = CreateSelectCount(sqlText);
            string sqlPager = string.Format("SELECT TOP {0} * FROM ({1}) _RowNum_Table_ WHERE _RowNum_>{2} {3}",
                pager.PageSize,
                CreateSelectRowNumber(sqlText, string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy),
                (pager.PageNow - 1) * pager.PageSize,
                string.Empty);//"ORDER BY " + (string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy));

            //查询分页
            Pager<T> tp = new Pager<T>();
            tp.Total = CPQuery.From(sqlCount, sqlParams)
                   .ExecuteScalar<int>();
            tp.Rows = CPQuery.From(sqlPager, sqlParams)
                .ToList<T>();
            return tp;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static Pager<T> GetPager<T>(Pager pager, string sqlText, params SqlParameter[] sqlParams)
            where T : class,new()
        {
            //查询和计算总数SQL
            string sqlCount = CreateSelectCount(sqlText);
            string sqlPager = string.Format("SELECT TOP {0} * FROM ({1}) _RowNum_Table_ WHERE _RowNum_>{2} {3}",
                pager.PageSize,
                CreateSelectRowNumber(sqlText, string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy),
                (pager.PageNow - 1) * pager.PageSize,
                string.Empty);//"ORDER BY " + (string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy));

            //查询分页
            Pager<T> tp = new Pager<T>();
            //这里会如果不用克隆，就会出现 SqlParameter已经被另外一个集合引用的异常。
            tp.Total = CPQuery.From(sqlCount, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                   .ExecuteScalar<int>();
            tp.Rows = CPQuery.From(sqlPager, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                  .ToList<T>();
            return tp;
        }


        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static TablePager GetPager(Pager pager, string sqlText, object sqlParams)
        {
            //查询和计算总数SQL
            string sqlCount = CreateSelectCount(sqlText);
            string sqlPager = string.Format("SELECT TOP {0} * FROM ({1}) _RowNum_Table_ WHERE _RowNum_>{2} {3}",
                pager.PageSize,
                CreateSelectRowNumber(sqlText, string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy),
                (pager.PageNow - 1) * pager.PageSize,
                string.Empty);//"ORDER BY " + (string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy));

            //查询分页
            TablePager tp = new TablePager();
            tp.Total = CPQuery.From(sqlCount, sqlParams)
                   .ExecuteScalar<int>();
            tp.Table = CPQuery.From(sqlPager, sqlParams)
                .FillDataTable();
            return tp;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="sqlText"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static TablePager GetPager(Pager pager, string sqlText, params SqlParameter[] sqlParams)
        {
            string order = string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy;
            order=order.Split(' ').Last();
            //查询和计算总数SQL
            string sqlCount = CreateSelectCount(sqlText);
            string sqlPager = string.Format("SELECT TOP {0} * FROM ({1}) _RowNum_Table_ WHERE _RowNum_>{2} {3}",
                pager.PageSize,
                CreateSelectRowNumber(sqlText, string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy),
                (pager.PageNow - 1) * pager.PageSize,
                "order by _RowNum_ asc");//"ORDER BY " + (string.IsNullOrEmpty(pager.OrderBy) ? pager.DefaultOrderBy : pager.OrderBy));

            //查询分页
            TablePager tp = new TablePager();
            //这里会如果不用克隆，就会出现 SqlParameter已经被另外一个集合引用的异常。
            tp.Total = CPQuery.From(sqlCount, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                   .ExecuteScalar<int>();
            tp.Table = CPQuery.From(sqlPager, sqlParams.Select(m => (SqlParameter)((ICloneable)m).Clone()).ToArray())
                  .FillDataTable();
            return tp;
        }

        #endregion
    }
}
